package vfs.persist.util;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.impl.GenericObjectPool;
import vfs.except.VFSPersistenceException;

/**
 *
 * @author Matthew Eavenson <durandal@uga.edu>
 */
public class VFSPersistUtil {
    
    /** The fully qualified name of the JDBC driver.
     */
    private static final String DB_DRIVER = "org.postgresql.Driver";

    /** The JDBC connection string/URL.
     */
    private static final String DB_CONNECTION_URL = "jdbc:postgresql://localhost:5432/postgres";

    /** The database user name.
     */
    private static final String DB_USERNAME = "postgres";

    /** The password for the database user.
     */
    private static final String DB_PASSWORD = "p0stp@ss";
    
    static{
        try {
            Class.forName (DB_DRIVER);
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException("VFSPersistUtils.connect: error unable to find Driver");
        }
        GenericObjectPool connectionPool = new GenericObjectPool(null);
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(DB_CONNECTION_URL, DB_USERNAME, DB_PASSWORD);
        new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
        PoolingDriver driver = new PoolingDriver();
        driver.registerPool("example",connectionPool);        
    }
    
    
    public static void disableAutoCommit(Connection conn){
        try{ conn.setAutoCommit(false);
        }catch(SQLException ex){ 
            throw new VFSPersistenceException(ex.getMessage()); 
        }
    }
    
    public static void enableAutoCommit(Connection conn){
        try{ conn.setAutoCommit(true);
        }catch(SQLException ex){ 
            throw new VFSPersistenceException(ex.getMessage()); }
    }
    
    public static void commit(Connection conn){
        try{ conn.commit();
        }catch(SQLException ex){ 
            throw new VFSPersistenceException(ex.getMessage()); }
    }
    
    public static void rollback(Connection conn){
        try{ conn.rollback();
        }catch(SQLException ex){ 
            throw new VFSPersistenceException(ex.getMessage()); }
    }
    
    /*****************************************************************************
     * Query the database.
     * @param query  a SQL query
     */
    public static ResultSet queryDB (Connection conn, String query){
        ResultSet rs = null;
        try{
            Statement stmt = conn.createStatement ();
            rs = stmt.executeQuery(query);
        }catch(SQLException sqe){
            System.out.println("\nQUERY DB: "+query+"\n");
            throw new VFSPersistenceException(sqe.getMessage());
        }
        return rs;
    }
    
    public static void updateDB(Connection conn, String query) throws VFSPersistenceException{
        updateDB(conn, query, false);
    }
    
    /*****************************************************************************
     * Update the database to either include/insert or exclude/delete a class T
     * object.
     * @param query  a SQL update query
     */
    public static long updateDB (Connection conn, String query, boolean returnKeys){
        long key = -1;
        try{
            Statement stmt = conn.createStatement ();
            if(returnKeys){
                stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
                ResultSet rs = stmt.getGeneratedKeys();
                if(rs.next()) key = rs.getLong(1);
            }else stmt.executeUpdate (query);
            
        }catch(SQLException sqe){
            System.out.println("\nUPDATE DB: "+query+"\n");
            throw new VFSPersistenceException(sqe.getMessage());
        }
        return key;
    }
    
    /*****************************************************************************
     * Establish a JDBC connection to the database.
     * @return  a database connection object
     */
    public static Connection connect (){
        
        try {
            return DriverManager.getConnection (DB_CONNECTION_URL,
                                                DB_USERNAME,
                                                DB_PASSWORD);
        } catch (SQLException ex) {
            throw new VFSPersistenceException("Error: unable to connect to database");
        }
    }

    
}
